﻿Imports System.Data.OleDb

Public Class Profile
    Inherits System.Web.UI.Page
    Protected Function getStars(ByVal stars As Integer)
        Dim retVal As String
        retVal = ""
        For i = 1 To stars
            retVal = retVal + "<img src=""star-done.jpg"" alt=""*""width=""20px"" height=""20px"">"
        Next
        Return retVal
    End Function

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Request.Params("TutorUserName") = User.Identity.Name Then
            btn_EditProfile.Visible = True
        Else
            btn_EditProfile.Visible = False
        End If
        'if page is reached from elsewhere, do the following
        If IsPostBack = False Then
            If Request.Params("TutorUserName") = "" Then Response.Redirect("Profile.aspx?TutorUserName=" & User.Identity.Name)
            Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("TutorsConnectionString").ConnectionString)
            Dim SqlString As String = "SELECT * FROM qryTutorComments WHERE TutorUserName=@f1"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", Request.Params("TutorUserName"))
            oleDbConn.Open()
            Dim dr = cmd.ExecuteReader()
            GridView1.DataSource = dr
            GridView1.DataBind()
        End If
    End Sub

    Protected Sub btn_AddComment_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_AddComment.Click

        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("TutorsConnectionString").ConnectionString)

        Dim SqlString1 As String = "SELECT * FROM tblTutorProfile WHERE TutorUserName=@f5"
        Dim cmd1 As OleDbCommand = New OleDbCommand(SqlString1, oleDbConn)
        cmd1.CommandType = CommandType.Text
        cmd1.Parameters.AddWithValue("@f5", Request.Params("TutorUserName"))
        oleDbConn.Open()
        Dim dr = cmd1.ExecuteReader()

        Dim SqlString As String = "Insert into tblComment(Author, Title, Comment, Tutor_fkID) Values (@f1,@f2,@f3,@f4)"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
        cmd.CommandType = CommandType.Text

        cmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        cmd.Parameters.AddWithValue("@f2", tb_CommentTitle.Text)
        cmd.Parameters.AddWithValue("@f3", tb_Comment.Text)
        dr.Read()
        If dr.HasRows Then
            cmd.Parameters.AddWithValue("@f4", dr("ID"))
            dr.Close()
        End If
        cmd.ExecuteNonQuery()

        '''''Fill GrdView1
        Dim fillComments As String = "SELECT * FROM qryTutorComments WHERE Tutor_fkID=@f1"
        Dim fillCommentsCmd As OleDbCommand = New OleDbCommand(fillComments, oleDbConn)
        fillCommentsCmd.CommandType = CommandType.Text
        fillCommentsCmd.Parameters.AddWithValue("@f1", Request.Params("TutorUserName"))
        Dim dr1 = fillCommentsCmd.ExecuteReader()
        GridView1.DataSource = dr1
        GridView1.DataBind()

        tb_CommentTitle.Text = ""
        tb_Comment.Text = ""
        MsgBox("Thank you for adding your comment")
        'Response.Redirect("Profile.aspx?TutorUserNamer")
        oleDbConn.Close()
    End Sub

    Protected Sub btn_AddTags_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_AddTags.Click

        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("TutorsConnectionString").ConnectionString)

        Dim SqlString1 As String = "SELECT * FROM tblTutorProfile WHERE TutorUserName=@f5"
        Dim cmd1 As OleDbCommand = New OleDbCommand(SqlString1, oleDbConn)
        cmd1.CommandType = CommandType.Text
        cmd1.Parameters.AddWithValue("@f5", Request.Params("TutorUserName"))
        oleDbConn.Open()
        Dim dr = cmd1.ExecuteReader()
        dr.Read()

        Dim SqlString As String = "Insert into tblTutor_Tag_Link(Tutor_fkID, Tag_fkID) Values (@f1,@f2)"
        For Each cb As ListItem In CheckBoxList1.Items
            If cb.Selected Then
                ' add each selected tag to tblTutor_Tag_Link
                Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("@f1", dr("ID"))
                cmd.Parameters.AddWithValue("@f2", cb.Value)
                cmd.ExecuteNonQuery()
            End If
        Next
        'cmd.Parameters.AddWithValue("@f1", tb_Author.Text)
        If tb_Tags.Text = "" Then Return
        Dim newTags = tb_Tags.Text.Split(",")
        For Each newTag In newTags
            'first add the tags to the tag table
            Dim newTagSql As String = "INSERT INTO tblTags (Tag) Values(@F1)"
            Dim cmd As OleDbCommand = New OleDbCommand(newTagSql, oleDbConn)
            cmd.CommandType = CommandType.Text

            cmd.Parameters.AddWithValue("@f1", newTag)
            cmd.ExecuteNonQuery()

            'Then find the ID of the new tag
            Dim getLastIDCmd As OleDbCommand = New OleDbCommand("SELECT Top 1 ID From tblTags ORDER by ID DESC", oleDbConn)
            Dim dataReader = getLastIDCmd.ExecuteReader()
            dataReader.Read()
            Dim lastID = dataReader.GetValue(0)

            'associate ID with Tutor ID
            Dim newCmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            newCmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", dr("ID"))
            newCmd.Parameters.AddWithValue("@f2", lastID)
            cmd.ExecuteNonQuery()
        Next
        tb_Tags.Text = ""
    End Sub

    Protected Sub btnSendRequest_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSendRequest.Click
        Dim SqlString As String
        Dim SqlString1 As String
        Dim SqlString2 As String
        Dim cmd As OleDbCommand
        Dim cmd1 As OleDbCommand
        Dim cmd2 As OleDbCommand

        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("TutorsConnectionString").ConnectionString)
        SqlString = "SELECT * FROM tblParents WHERE TuteeID=@f1"
        cmd = New OleDbCommand(SqlString, oleDbConn)
        SqlString1 = "SELECT * FROM tblTutorProfile WHERE TutorUserName=@f2"
        cmd1 = New OleDbCommand(SqlString1, oleDbConn)
        cmd.CommandType = CommandType.Text
        cmd1.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        cmd1.Parameters.AddWithValue("@f2", Request.Params("TutorUserName"))
        oleDbConn.Open()
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        Dim dr1 As OleDbDataReader = cmd1.ExecuteReader()

        SqlString2 = "INSERT into tblTutorParentRequest (ParentFk_ID, TutorFk_ID, Message) VALUES(@f1, @f2, @f3)"
        cmd2 = New OleDbCommand(SqlString2, oleDbConn)
        dr.Read()
        cmd2.Parameters.AddWithValue("@f1", dr("ID"))
        dr.Close()
        dr1.Read()
        cmd2.Parameters.AddWithValue("@f2", dr1("ID"))
        cmd2.Parameters.AddWithValue("@f3", tbRequestComment.Text)
        tbRequestComment.Text = ""
        cmd2.ExecuteNonQuery()
        dr.Close()
        cmd.Dispose()
        cmd1.Dispose()
        cmd2.Dispose()
        oleDbConn.Close()
        oleDbConn.Dispose()
        MsgBox("Thank you, your request has been sent")



    End Sub

    Protected Sub btnAddRating_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAddRating.Click
        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("TutorsConnectionString").ConnectionString)
        oleDbConn.Open()

        Dim SqlString1 As String = "SELECT * FROM tblTutorProfile WHERE TutorUserName=@f1"
        Dim cmd As New OleDbCommand(SqlString1, oleDbConn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", Request.Params("TutorUserName"))
        Dim dr As OleDbDataReader = cmd.ExecuteReader()

        Dim SqlString2 As String = "SELECT * FROM tblParents WHERE TuteeID=@f1"
        Dim cmd1 As New OleDbCommand(SqlString2, oleDbConn)
        cmd1.CommandType = CommandType.Text
        cmd1.Parameters.AddWithValue("@f1", User.Identity.Name)
        Dim dr1 As OleDbDataReader = cmd1.ExecuteReader()

        Dim delQuotationRatingSql As String = "DELETE FROM tblTutorUserRating WHERE TutorID=@F1 AND Tutee=@F2"
        Dim delQuotationRatingCmd As New OleDbCommand(delQuotationRatingSql, oleDbConn)

        dr.Read()
        delQuotationRatingCmd.Parameters.AddWithValue("@f1", dr("ID"))
        'dr.Close()
        dr1.Read()
        delQuotationRatingCmd.Parameters.AddWithValue("@f2", dr1("ID"))
        delQuotationRatingCmd.ExecuteNonQuery()
        'dr1.Close()

        Dim SqlString As String = "INSERT INTO tblTutorUserRating (TutorID, Tutee, Rating) VALUES (@F1,@F2,@F3)"
        Dim addRatingCmd As New OleDbCommand(SqlString, oleDbConn)
        'dr.Read()
        addRatingCmd.Parameters.AddWithValue("@f1", dr("ID"))
        dr.Close()
        'dr1.Read()
        addRatingCmd.Parameters.AddWithValue("@f2", dr1("ID"))
        addRatingCmd.Parameters.AddWithValue("@f3", Rating1.CurrentRating)
        addRatingCmd.ExecuteNonQuery()
        dr1.Close()
        oleDbConn.Close()
        MsgBox("Thank you for adding your rating")

    End Sub
End Class






